INSERT INTO #BSOL_OF_tbStaging_SUS_Data (
[indicator_id]
, [start_date]
, [end_date]
, [numerator]
, [denominator]
, [indicator_value]
, [lower_ci95]
, [upper_ci95]
, [imd_code]
, [aggregation_id]
, [age_group_code]
, [sex_code]
, [ethnicity_code]
, [creation_date]
, [value_type_code]
, [source_code]
)
-- Ward Geography
(SELECT
T1.[indicator_id]
, CAST(LEFT(T1.[financial_year], 4) + '-04-01' AS DATE) AS start_date
, CAST('20' + RIGHT(T1.[financial_year], 2) + '-03-31' AS DATE) AS end_date
, SUM(T1.[numerator]) AS numerator
, CAST(NULL AS INT) AS denominator
, CAST(NULL AS NUMERIC) AS indicator_value
, CAST(NULL AS NUMERIC) AS lower_ci95
, CAST(NULL AS NUMERIC) AS upper_ci95
, T1.[imd] AS imd_code
, T1.[ward_code] AS aggregation_id
, T1.[age] AS age_group_code
, T1.[sex] AS sex_code
, T1.[ethnicity_code]
, CAST(CURRENT_TIMESTAMP AS DATE) AS creation_date
, '4' AS value_type_code --DASR
, '1' AS source_code -- SQL
FROM #BSOL_OF_tbStaging_NumeratorData T1
GROUP BY
T1.[indicator_id]
, CAST(LEFT(T1.[financial_year], 4) + '-04-01' AS DATE)
, CAST('20' + RIGHT(T1.[financial_year], 2) + '-03-31' AS DATE)
, T1.[imd]
, T1.[ward_code]
, T1.[sex]
, T1.[age]
, T1.[ethnicity_code]
UNION
-- LAD Geography
SELECT
T1.[indicator_id]
, CAST(LEFT(T1.financial_year, 4) + '-04-01' AS DATE) AS start_date
, CAST('20' + RIGHT(T1.financial_year, 2) + '-03-31' AS DATE) AS end_date
, SUM(T1.numerator) AS numerator
, CAST(NULL AS INT) AS denominator
, CAST(NULL AS NUMERIC) AS indicator_value
, CAST(NULL AS NUMERIC) AS lower_ci95
, CAST(NULL AS NUMERIC) AS upper_ci95
, T1.[imd] AS imd_code
, T1.[lad_code] AS aggregation_id
, T1.[age] AS gae_group_code
, t1.[sex] AS sex_code
, T1.[ethnicity_code] AS ethnicity_code
, CAST(CURRENT_TIMESTAMP AS date) AS creation_date
, '4' AS value_type_code --DASR
, '1' AS source_code
FROM #BSOL_OF_tbStaging_NumeratorData T1
GROUP BY
T1.[indicator_id]
, CAST(LEFT(T1.[financial_year], 4) + '-04-01' AS DATE)
, CAST('20' + RIGHT(T1.[financial_year], 2) + '-03-31' AS DATE)
, T1.[imd]
, T1.[lad_code]
, T1.[age]
, T1.[sex]
, T1.[ethnicity_code]
UNION
--Locality Geography
SELECT
T1.[indicator_id]
, CAST(LEFT(T1.[financial_year], 4) + '-04-01' AS DATE) AS start_date
, CAST('20' + RIGHT(T1.[financial_year], 2) + '-03-31' AS DATE) AS end_date
, SUM(T1.[numerator]) AS numerator
, CAST(NULL AS INT) AS denominator
, CAST(NULL AS NUMERIC) AS indicator_value
, CAST(NULL AS NUMERIC) AS lower_ci95
, CAST(NULL AS NUMERIC) AS upper_ci95
, T1.[imd] AS imd_code
, T1.[locality_res] AS aggregation_id
, T1.[age] AS age_group_code
, T1.[sex] AS sex_code
, T1.[ethnicity_code]
, CAST(CURRENT_TIMESTAMP AS DATE) AS creation_date
, '4' AS value_type_code --DASR
, '1' AS source_code -- SQL
FROM #BSOL_OF_tbStaging_NumeratorData T1
WHERE [locality_res] <> 'Non-bsol'
GROUP BY
T1.[indicator_id]
, CAST(LEFT(T1.[financial_year], 4) + '-04-01' AS DATE)
, CAST('20' + RIGHT(T1.[financial_year], 2) + '-03-31' AS DATE)
, T1.[imd]
, T1.[locality_res]
, T1.[age]
, T1.[sex]
, T1.[ethnicity_code]
UNION
-- ICB geography
SELECT
T1.[indicator_id]
, CAST(LEFT(T1.[financial_year], 4) + '-04-01' AS DATE) AS start_date
, CAST('20' + RIGHT(T1.[financial_year], 2) + '-03-31' AS DATE) AS end_date
, SUM(T1.[numerator]) AS numerator
, CAST(NULL AS INT) AS denominator
, CAST(NULL AS NUMERIC) AS indicator_value
, CAST(NULL AS NUMERIC) AS lower_ci95
, CAST(NULL AS NUMERIC) AS upper_ci95
, T1.[imd] AS imd_code
, '151' AS aggregation_id -- ICB Level
, T1.[age] AS age_group_code
, T1.[sex] AS sex_code
, T1.[ethnicity_code]
, CAST(CURRENT_TIMESTAMP AS DATE) AS creation_date
, '4' AS value_type_code --DASR
, '1' AS source_code -- SQL
FROM #BSOL_OF_tbStaging_NumeratorData T1
GROUP BY
T1.[indicator_id]
, CAST(LEFT(T1.[financial_year], 4) + '-04-01' AS DATE)
, CAST('20' + RIGHT(T1.[financial_year], 2) + '-03-31' AS DATE)
, T1.[imd]
, T1.[age]
, T1.[sex]
, T1.[ethnicity_code]
)